/**
 * DoaModel.java
 * 
 * author: Zhiqiang Zhu
 * date: Nov/27/2020
 */
package models;

import java.sql.*;
import records.BankRecords;

/**
 * class DaoModel The class daoModel allows to process all necessary CRUD
 * operations.
 */
public class DaoModel {
	DBConnect conn = null;
	Statement stmt = null;

	String tableName = "zhuzhiqiang_lab4_tab";

	public DaoModel() {
		conn = new DBConnect();
	}

	public void createTable() {
		try {

			// Open a connection
			System.out.println("Connecting to database to create Table...");
			System.out.println("Connected database successfully...");

			ResultSet rs = conn.connect().getMetaData().getTables(null, null, tableName, null);
			// if table already exist
			if (!rs.next()) {
				// Execute create query
				System.out.println("Creating table in given database...");

				stmt = conn.connect().createStatement();

				String sql = "CREATE TABLE " + tableName + " (pid INTEGER not NULL AUTO_INCREMENT, "
						+ " id VARCHAR(10), " + " age INTEGER," + " sex VARCHAR(8)," + " region VARCHAR(12),"
						+ " income numeric(8,2), " + " married VARCHAR(3)," + " children INTEGER," + " car VARCHAR(4),"
						+ " save_act VARCHAR(4), " + " current_act VARCHAR(4), " + " mortgage VARCHAR(4), "
						+ " pep VARCHAR(4), " + " PRIMARY KEY ( pid ))";

				stmt.executeUpdate(sql);
				System.out.println("Created table in given database...");
			} else {
				System.out.println("The table already exists, skip creating a new table.");
			}

			conn.connect().close(); // close db connection
		} catch (SQLException se) { // Handle errors for JDBC
			se.printStackTrace();
		}
	}

	/**
	 * insertRecords() inserts all bank records data into database.
	 */
	public void insertRecords(BankRecords[] robjs) {
		try {
			// Execute a query
			System.out.println("Inserting records into the table...");
			stmt = conn.connect().createStatement();
			String sql = null;

			// Delete old records...
			sql = "DELETE FROM " + tableName;
			stmt.executeUpdate(sql);

			// Prepared Statement
			String psSql = "INSERT INTO " + tableName
					+ " (id, age, sex, region, income, married, children, car, mortgage, pep) "
					+ "VALUES ( ?,?,?,?,?,?,?,?,?,? )";
			PreparedStatement pstmt = conn.connect().prepareStatement(psSql);

			// Include all object data to the database table
			int recCount = 0;
			for (int i = 0; i < robjs.length; ++i) {
				pstmt.setString(1, robjs[i].getId());
				pstmt.setInt(2, robjs[i].getAge());
				pstmt.setString(3, robjs[i].getSex());
				pstmt.setString(4, robjs[i].getRegion());
				pstmt.setDouble(5, robjs[i].getIncome());
				pstmt.setString(6, robjs[i].getMarried());
				pstmt.setInt(7, robjs[i].getChildren());
				pstmt.setString(8, robjs[i].getCar());
				pstmt.setString(9,  robjs[i].getMortgage());
				pstmt.setString(10,  robjs[i].getPep());
				
				pstmt.executeUpdate();

				recCount++;
				//System.out.print(".");
			}
			System.out.printf("%d records were inserted.\n", recCount);
			conn.connect().close();
		} catch (SQLException se) {
			se.printStackTrace();
		}
	}// INSERT INTO METHOD

	public ResultSet retrieveRecords() throws SQLException {
		ResultSet rs = null;

		stmt = conn.connect().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

		String sql = "SELECT pid, id,income, pep from " + tableName + " order by pep desc";
		rs = stmt.executeQuery(sql);
		conn.connect().close();
		return rs;
	}

}
